﻿using System;
using System.Collections.Generic;
using System.Text;
using Cashier_Model;
using System.Data;
using Cashier_DAO.db;
using System.Collections;
using System.Data.SQLite;
using System.Data.Common;

namespace Cashier_DAO
{
    public class MemberDAO : DAOFactory
    {
        /// <summary>
        /// 获取Member信息
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="member"></param>
        /// <returns></returns>
        public Member GetMember(DbConnection conn, Member member)
        {
            Member result = null;
            try
            {              
                StringBuilder sb = new StringBuilder("select * from member where id>0");
                Hashtable paramtable = new Hashtable();
                this.AddParameters(sb, paramtable, member);

                if (paramtable.Keys.Count > 0)
                {
                    DbCommand cmd = CommandFactory.CreateCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = sb.ToString();
                    this.AddCommandParamters(cmd, paramtable);

                    DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                    DataTable dt = new DataTable("member");
                    da.Fill(dt);
                    if (dt != null && dt.Rows.Count == 1)
                    {
                        DataRow row = dt.Rows[0];
                        result = new Member();
                        this.DataRowToModel(row, result);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 查询Member数据列表
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="member"></param>
        /// <returns></returns>
        public DataTable GetMemberTable(DbConnection conn, Member member)
        {
            DataTable dt = null;
            try
            {
                StringBuilder sb = new StringBuilder("select * from member where id>0");
                Hashtable paramtable = new Hashtable();
                this.AddParameters(sb, paramtable, member);

                this.GenerateOrderAndPageSQL(sb, paramtable, member);

                DbCommand cmd = CommandFactory.CreateCommand();
                cmd.Connection = conn;
                cmd.CommandText = sb.ToString();
                this.AddCommandParamters(cmd, paramtable);
                
                dt = new DataTable("member");
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 查询会员记录数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="member"></param>
        /// <returns></returns>
        public long GetMemberRecord(DbConnection conn, Member member)
        {
            long record = 0;
            try
            {
                StringBuilder sb = new StringBuilder("select count(id) as c from member where id>0");
                Hashtable paramtable = new Hashtable();
                this.AddParameters(sb, paramtable, member);

                DbCommand cmd = CommandFactory.CreateCommand();
                cmd.Connection = conn;
                cmd.CommandText = sb.ToString();
                this.AddCommandParamters(cmd, paramtable);

                Object obj = cmd.ExecuteScalar();
                if (obj != null)
                    record = (long)obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return record;
        }
        
        /// <summary>
        /// 插入Member数据
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="member"></param>
        /// <returns></returns>
        public long InsertMember(DbConnection conn, Member member)
        {
            long id = 0;
            try
            {
                id = (Int64)this.Insert(conn, member);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return id;
        }

        /// <summary>
        /// 更新Member信息
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="member"></param>
        /// <returns></returns>
        public int UpdateMemeber(DbConnection conn, Member member)
        {
            int flag = 0;
            try
            {
                flag = this.Update(conn, member);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// 删除Member
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="member"></param>
        /// <returns></returns>
        public int DeleteMember(DbConnection conn, Member member)
        {
            int flag = 0;
            try
            {
                flag = this.Delete(conn, member);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// 更新用户积分
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="member"></param>
        /// <returns></returns>
        public int UpdateMemberIntegral(DbConnection conn, Member member)
        {
            int flag = 0;
            try
            {
                String sql = "update member set history_integral=@historyIntegral,current_integral=@currentIntegral where member_no=@memberno";
                DbCommand cmd = CommandFactory.CreateCommand(sql, conn);
                Hashtable paramtable = new Hashtable();
                paramtable.Add("historyIntegral", member.HistoryIntegral);
                paramtable.Add("currentIntegral", member.CurrentIntegral);
                paramtable.Add("memberno", member.Memberno);
                this.AddCommandParamters(cmd, paramtable);
                flag = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// 更新用户余额
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="member"></param>
        /// <returns></returns>
        public int UpdateMemberBalance(DbConnection conn, Member member)
        {
            int flag = 0;
            try
            {
                String sql = "update member set balance=@balance where member_no=@memberno";
                DbCommand cmd = CommandFactory.CreateCommand(sql, conn);
                Hashtable paramtable = new Hashtable();
                paramtable.Add("balance", member.Balance);
                paramtable.Add("memberno", member.Memberno);
                this.AddCommandParamters(cmd, paramtable);
                flag = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}
